﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Catering.View_Operaction;
using System.Data;
using System.Text;
using System.Collections;
using System.Web.Services;
using Catering_Rules;
using Microsoft.Office.Core;
using System.IO;
using Catering_DBOpertion;

namespace Catering.Catering_Page.richang
{
    public partial class ComprehensiveQuery : System.Web.UI.Page
    {
        private static AttendanceRecord ar = new AttendanceRecord();
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void But_ComprehensiveQuery_Seacher_Click(object sender, EventArgs e)
        {
            string AREAID = UCDDLToolWithDate1.GetDDL_Area();
            string CLASSID = UCDDLToolWithDate1.GetDDL_Class();
            DateTime DTIME = UCDDLToolWithDate1.GetDateTime();
            string NAME = txt_name.Text;            
            string pid = "0";
            if (Session["UserID"] != null) pid = Session["UserID"].ToString();
            DataTable dt = GetComprehensiveQuery(pid, AREAID, CLASSID, DTIME, NAME);
            Repeater1.DataSource = dt;
            Repeater1.DataBind();
        }
        public DataSet SelConNameByConID(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 CName");
            strSql.Append(" FROM Contractor ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where id = " + int.Parse(strWhere));
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 综合查询
        /// </summary>
        /// <param name="AREAID">作业区ID</param>
        /// <param name="CLASSID">班组ID</param>
        /// <param name="DTIME">日期</param>
        /// <param name="NAME">名字</param>
        /// <returns></returns>
        public DataTable GetComprehensiveQuery(string pid, string AREAID, string CLASSID, DateTime DTIME, string NAME)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(string.Format("select * from Personnel_AttendanceRecord where 1 = 1"));
            if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
            {
                strSql.Append(string.Format(" and AreaID = {0}", AREAID));
            }
            else
            {
                string strarealist = new Catering_Rules.OperationArea().GetStrAreaIDListByPersonnelID(pid);
                if (!string.IsNullOrEmpty(strarealist))
                {
                    strSql.Append(string.Format(" AND AreaID IN ({0}) ", strarealist));
                }
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                strSql.Append(string.Format(" and ClassID = {0}", CLASSID));
            }
            if (DTIME != null && !DTIME.Year.Equals("1949"))
            {
                strSql.Append(string.Format(" and Year(Date) = {0}", DTIME.Year));
                if (!DTIME.Second.Equals(11))
                {
                    strSql.Append(string.Format(" and Month(Date) = {0}", DTIME.Month));
                }
            }
            if (!string.IsNullOrEmpty(NAME))
            {
                strSql.Append(string.Format(" and PName = '{0}'", NAME));
            }
            //if (!string.IsNullOrEmpty(PNAME))
            //{
            //    where.Append(string.Format(" and Name like '%{0}%'", PNAME));
            //}
            DataTable dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            DataTable tempdt = new DataTable();
            tempdt.Columns.Add("tempid", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("Name", typeof(string));//增加字段ClassID
            tempdt.Columns.Add("PName", typeof(string));//增加字段ClassID
            tempdt.Columns.Add("D1", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D2", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D3", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D4", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D5", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D6", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D7", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D8", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D9", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D10", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D11", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D12", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D13", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D14", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D15", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D16", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D17", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D18", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D19", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D20", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D21", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D22", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D23", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D24", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D25", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D26", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D27", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D28", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D29", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D30", typeof(int));//增加字段ClassID
            tempdt.Columns.Add("D31", typeof(int));//增加字段ClassID
            
            for (int i = 1; i <=12; i++)
            {
                DataRow dr_Plan = tempdt.NewRow();
                dr_Plan["tempid"] = i;
                
                for (int j = 0; j < dt.Rows.Count ; j++)
                {
                    int tempMonth = DateTime.Parse( dt.Rows[j]["Date"].ToString()).Month;
                    int tempDay = DateTime.Parse( dt.Rows[j]["Date"].ToString()).Day;
                    if (tempMonth == i)
                    {
                        dr_Plan["D" + tempDay.ToString()] = 1;                        
                    }
                }
                tempdt.Rows.Add(dr_Plan);
            }            
            dt.Dispose();
            return tempdt;
        }
    }
}